
from sqlalchemy import func
from core.file.helpers import get_signed_file_url
from models.engine import db
from models.online_mall.prod_other import MProdProperty
from models.online_mall.product_cart import MProduct
from models.types import StringUUID

class MBrand(db.Model):
    __tablename__ = "sp_brand"
    __table_args__ = (
        db.PrimaryKeyConstraint("id", name="sp_brand_pkey"),
        db.Index("sp_brand_name_idx", "name"),
    )
    id = db.Column(StringUUID,server_default=db.text('uuid_generate_v4()'),primary_key=True,comment="ID")
    tenant_id = db.Column(StringUUID, nullable=False,comment="租户ID")
    name = db.Column(db.String(64), nullable=False,comment="名称")
    pic_file_id = db.Column(StringUUID, nullable=True,comment="图片文件ID")
    memo = db.Column(db.String(255), nullable=True,comment="备注")
    seq = db.Column(db.Integer, nullable=False,server_default=db.text('0'),comment="排序")
    status = db.Column(db.Boolean, nullable=False,server_default=db.text('true'),comment="上线状态")
    brief = db.Column(db.String(128), nullable=True,comment="简介")
    content = db.Column(db.Text, nullable=True,comment="品牌介绍")
    first_char = db.Column(db.String(1), nullable=True,comment="首字母")
    created_at = db.Column(db.TIMESTAMP, nullable=False,server_default=func.current_timestamp(),comment="创建时间")
    created_by = db.Column(StringUUID, nullable=True,comment="创建人")
    updated_at = db.Column(db.TIMESTAMP, nullable=True,comment="更新时间")
    updated_by = db.Column(StringUUID, nullable=True,comment="更新人")


class MCategoryBrand(db.Model):
    __tablename__ = "sp_category_brand"
    __table_args__ = (
        db.PrimaryKeyConstraint("id", name="sp_category_brand_pkey"),
        db.Index("sp_category_brand_brand_id_idx", "brand_id"),
        db.Index("sp_category_brand_category_id_idx", "category_id"),
    )
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True,comment="ID")
    category_id = db.Column(StringUUID, nullable=False,comment="分类ID")
    brand_id = db.Column(StringUUID, nullable=False,comment="品牌ID")


class MCategoryProperty(db.Model):
    """
    商品分类-属性
    """
    __tablename__ = "sp_category_prop"
    __table_args__ = (
        db.PrimaryKeyConstraint("id", name="sp_category_prop_pkey"),
        db.Index("sp_category_prop_category_id_idx", "category_id"),
    )
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True,comment="ID")
    category_id = db.Column(StringUUID, nullable=False,comment="商品分类ID")
    prop_id = db.Column(db.BigInteger, nullable=False,comment="属性ID")

class MProdCategory(db.Model):
    __tablename__ = "sp_category"
    __table_args__ = (
        db.PrimaryKeyConstraint("id", name="sp_category_pkey"),
        db.Index("sp_category_parent_id_idx", "parent_id"),
        db.Index("sp_category_shop_idx", "shop_id"),
    )
    id = db.Column(StringUUID, primary_key=True,server_default=db.text("uuid_generate_v4()"),comment="ID")
    tenant_id = db.Column(StringUUID, nullable=False,comment="租户ID")
    shop_id = db.Column(StringUUID, nullable=False,comment="店铺ID")
    parent_id = db.Column(StringUUID, nullable=False,comment="父级分类ID:0:无")
    name = db.Column(db.String(64), nullable=False,comment="分类名称")
    icon_file_id = db.Column(StringUUID, nullable=True,comment="显示图标文件ID")
    pic_file_id = db.Column(StringUUID, nullable=True,comment="显示图片文件ID")
    seq = db.Column(db.Integer, nullable=False,server_default=db.text('0'),comment="排序序号")
    status = db.Column(db.Boolean, nullable=False,server_default=db.text('true'),comment="上线状态")
    level = db.Column(db.Integer, nullable=False,server_default=db.text('0'),comment="层级")
    created_at = db.Column(db.TIMESTAMP, nullable=False,server_default=func.current_timestamp(),comment="创建时间")
    created_by = db.Column(StringUUID, nullable=True,comment="创建人")
    updated_at = db.Column(db.TIMESTAMP, nullable=True,comment="更新时间")
    updated_by = db.Column(StringUUID, nullable=True,comment="更新人")

    @property
    def IconURL(self)->str|None:
        if self.icon_file_id:
            return get_signed_file_url(self.icon_file_id)
        return None

    @property
    def PicURL(self)->str|None:
        if self.pic_file_id:
            return get_signed_file_url(self.pic_file_id)
        return None
    
    @property
    def brands(self)->list[MBrand]:
        return db.session.query(MBrand).join(MCategoryBrand,MCategoryBrand.brand_id==MBrand.id).\
            filter(MCategoryBrand.category_id==self.id).all()
    

    @property
    def prod_props(self)->list[MProdProperty]:
        return db.session.query(MProdProperty).join(MCategoryProperty,MCategoryProperty.prop_id==MProdProperty.id).\
            filter(MCategoryProperty.category_id==self.id).all()
    
    @property
    def products(self)->list[MProduct]:
        return db.session.query(MProduct).filter(MProduct.category_id==self.id).all()

    @property
    def categories(self)->list['MProdCategory']:
        return db.session.query(MProdCategory).filter(MProdCategory.parent_id==self.id).all()
